﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;
using Lamex.Interface;
using Lamex.Base;
using System.Diagnostics;

namespace Lamex.mCost.UI
{
    public partial class frmCOST_MOTP_Refresh_From_SAP : frmBaseChild
    {
        //此项目用到sapnco_utils.dll、sapnco.dll两个第三方dll，暂只提供64位版本
        string strSql;
        DataTable dt;

        public frmCOST_MOTP_Refresh_From_SAP()
        {
            InitializeComponent();
        }

        private void frmCOST_MOTP_Refresh_From_SAP_Resize(object sender, EventArgs e)
        {
            splitContainerControl1.SplitterPosition = this.Width / 2;
        }

        private void frmCOST_MOTP_Refresh_From_SAP_Load(object sender, EventArgs e)
        {
            try { base.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理
            refreshNoTPList();//刷新无MOTP清单
        }

        private void refreshNoTPList()
        {
            try
            {
                //只刷新编号长度为12位的MO单，即只刷新6字头，不刷新1字头MO
                strSql = @"SELECT Package_Mo AS MO_No,Material_No AS 物料编号,Product_TS AS 生产日期,TP_TYPE AS TP类型,TP_PRICE AS TP单价,
PROD_LINE AS 生产线,MISS_MAT AS 无COST物料,ERR_INFO AS 错误信息,UPDATE_TIME AS 刷新时间
FROM
(SELECT Package_Mo,Material_No,Product_TS
FROM [172.23.134.36].[LamexMESManager].[dbo].[Transfer_Package_List]
WHERE LEN(Package_Mo)=12) AS a
LEFT JOIN Transfer_Package_MO_TP AS b
ON a.Package_Mo=b.AUFNR
WHERE TP_PRICE=0 OR TP_PRICE IS NULL";

                dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(strSql);//取成本TP专用数据库数据
                addDataToGridControl(dt, gridControl1, gridView1);

                string strCurUser = Lamex.Common.Loginer.CurrentUser.AccountName;

                if (strCurUser == "何应书" || strCurUser == "刘邦泉" || strCurUser == "陈子锐")
                {
                    strSql = @"SELECT AUFNR AS MO_No,MATNR AS 物料编号,BUDAT AS 生产日期,TP_TYPE AS TP类型,TP_PRICE AS TP单价,
PROD_LINE AS 生产线,UPDATE_TIME AS 刷新时间
FROM Transfer_Package_MO_TP
WHERE DateDiff(dd,UPDATE_TIME,getdate())=0";
                }
                else
                {
                    //供其他用户浏览用，数据并未完全同步（172.23.134.36.41]与36服务器Transfer_Package_MO_TP数据不完全一致）
                    strSql = @"SELECT AUFNR AS MO_No,MATNR AS 物料编号,BUDAT AS 生产日期,TP_TYPE AS TP类型,'*.*' AS TP单价,
PROD_LINE AS 生产线,UPDATE_TIME AS 刷新时间
FROM [172.23.134.36].[LamexMESManager].[dbo].[Transfer_Package_MO_TP]
WHERE DateDiff(dd,UPDATE_TIME,getdate())=0";
                }

                //dt = Lamex.Base.BaseBuildSql.GetDataTableBySql(strSql);
                dt = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(strSql);//取成本TP专用数据库数据
                addDataToGridControl(dt, gridControl2, gridView2);

            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void addDataToGridControl(DataTable dt, DevExpress.XtraGrid.GridControl gc, DevExpress.XtraGrid.Views.Grid.GridView gv)
        {
            //if (dt.Rows.Count > 0)
            //{
            gc.DataSource = dt;
            gv.OptionsBehavior.Editable = false;
            gv.OptionsView.ColumnAutoWidth = true;
            //}

            //gridView1页脚汇总设置
            gv.Columns[0].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
            gv.Columns[0].SummaryItem.DisplayFormat = "计数:";
            gv.Columns[1].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
            //gv.Columns[1].SummaryItem.DisplayFormat = "{0:N}";//两位小数
            gv.Columns[1].SummaryItem.DisplayFormat = "{0:N0}";//无小数位
        }

        private void getMOTP_From_SAP()
        {
            //遍历gridView1数据，并从SAP系统中获取MOTP，传入[172.23.134.41].[LamexMES_TransferTP].[Transfer_Package_MO_TP]
            //TO DO......
            if (gridView1.Columns.Count <= 0)
                return;
            Stopwatch sw = new Stopwatch();
            sw.Start();
            DataTable dataTable = ((DataView)gridView1.DataSource).ToTable();
            DataTable dt_MES = Common.GetTable.GetMES_TABLE();
            DataTable dt_SAP = new DataTable();
            List<string> ListTemp = new List<string>();
            if (dataTable.Rows.Count > 0)
            {
                //重定义表列名，便于与SAP对接
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    DataRow dr = dt_MES.NewRow();
                    dr["AUFNR"] = dataTable.Rows[i]["MO_No"].ToString();
                    dr["MATNR"] = dataTable.Rows[i]["物料编号"].ToString();
                    dr["BUDAT"] = Convert.ToDateTime(dataTable.Rows[i]["生产日期"]).ToString("yyyy-MM-dd");
                    dt_MES.Rows.Add(dr);
                    ListTemp.Add(dataTable.Rows[i]["MO_No"].ToString());
                }
                //获取SAP回传的DataTable
                dt_SAP = getSAP_Data(dt_MES);
                if (dt_SAP.Rows.Count > 0)
                {

                    //SAP同时传一份无TP值([TP_TYPE]、[TP_PRICE]、[PROD_LINE]处理成NULL)的数据到[172.23.134.36]，供开发人员测试检查用
                    //[172.23.134.36].[Transfer_Package_MO_TP]未设[AUFNR]主键，所以会有重复数据
                    //添加导入时间和导入用户
                    DataColumn dc1 = new DataColumn("UPDATE_TIME", typeof(DateTime));
                    dc1.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    dt_SAP.Columns.Add(dc1);
                    DataColumn dc2 = new DataColumn("UPDATE_USER", typeof(string));
                    dc2.DefaultValue = Lamex.Common.Loginer.CurrentUser.AccountName;
                    dt_SAP.Columns.Add(dc2);

                    //插入前先删除
                    StringBuilder sbFilter1 = new StringBuilder();
                    sbFilter1.AppendFormat(@" AND AUFNR IN ('{0}')", string.Join("','", ListTemp.ToArray()));
                    string sqlDel = @"delete  FROM [LamexMESManager].[dbo].[Transfer_Package_MO_TP] where 1=1  " + sbFilter1 + " ";
                    BaseBuildSql.ExecuteNonQuery(sqlDel);
                    //插入数据库-[172.23.134.36]
                    if (BaseBuildSql.ExcuteInsert(dt_SAP, "Transfer_Package_MO_TP"))
                    {
                        sw.Stop();
                        TimeSpan tt = sw.Elapsed;
                        DevExpress.XtraEditors.XtraMessageBox.Show("数据刷新成功，成功" + dt_SAP.Rows.Count + "条，共用时" + Math.Round(tt.TotalSeconds, 1) + "秒！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        DevExpress.XtraEditors.XtraMessageBox.Show("数据插入MES系统失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }

                //if (getSAP_Data(dt_MES,ListTemp))
                //{
                //    sw.Stop();
                //    TimeSpan tt = sw.Elapsed;
                //    DevExpress.XtraEditors.XtraMessageBox.Show("数据刷新成功，成功" + dt_MES.Rows.Count + "条，共用时" + Math.Round(tt.TotalSeconds, 1) + "秒！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                //}
                //else
                //{
                //    DevExpress.XtraEditors.XtraMessageBox.Show("无法取得SAP数据,导入失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //}
            }

            //再次刷新无TP MO清单
            refreshNoTPList();
        }

        public DataTable getSAP_Data(DataTable table)
        {
            using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在从SAP下载数据......", new Size(400, 80)))
            {
                string strError = "";
                Common.SAPHelper sAPHelper = new Common.SAPHelper();
                //从SAP获取数据并插入172.23.134.41数据库--SAP直接插入
                DataTable dataTable = new DataTable();
                dataTable = sAPHelper.GetSAPDataTable(table,
                    "I_TAB",
                    Common.GetTable.GetSAP_TABLE(),
                    "ZFM_MES_GET_TP",
                    Common.GetTable.GetSAP_LIST()
                    , "LXP"
                    , out strError);
                if (strError.Trim().Length > 0)
                    DevExpress.XtraEditors.XtraMessageBox.Show("无法取得SAP数据,导入失败！\r\n" + strError, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return dataTable;
                //if (dataTable.Rows.Count <= 0)
                //    return false;            

                ////SAP同时传一份无TP值([TP_TYPE]、[TP_PRICE]、[PROD_LINE]处理成NULL)的数据到[172.23.134.36]，供开发人员测试检查用
                ////[172.23.134.36].[Transfer_Package_MO_TP]未设[AUFNR]主键，所以会有重复数据
                ////添加导入时间和导入用户
                //DataColumn dc1 = new DataColumn("UPDATE_TIME", typeof(DateTime));
                //dc1.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                //dataTable.Columns.Add(dc1);
                //DataColumn dc2 = new DataColumn("UPDATE_USER", typeof(string));
                //dc2.DefaultValue = Lamex.Common.Loginer.CurrentUser.AccountName;
                //dataTable.Columns.Add(dc2);

                ////插入前先删除
                //StringBuilder sbFilter1 = new StringBuilder();
                //sbFilter1.AppendFormat(@" AND AUFNR IN ('{0}')", string.Join("','", ListTemp.ToArray()));
                //string sqlDel = @"delete  FROM [LamexMESManager].[dbo].[Transfer_Package_MO_TP] where 1=1  " + sbFilter1 + " ";
                //BaseBuildSql.ExecuteNonQuery(sqlDel);
                ////插入数据库-[172.23.134.36]
                //if (BaseBuildSql.ExcuteInsert(dataTable, "Transfer_Package_MO_TP"))
                //    return true;
                //else
                //    return false;
            }
        }

        private void sbtnView_Click(object sender, EventArgs e)
        {
            DoView(null);//前面不能用加base;
        }

        public override void DoView(IButtonInfo sender)
        {
            getMOTP_From_SAP();
            base.DoView(sender);
        }

        public override void DoExport(IButtonInfo sender)
        {
            Lamex.Common.ExecelHelper.GridViewToExcel(gridView1);
            base.DoExport(sender);
        }

        private void 导无TP清单ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Lamex.Common.ExecelHelper.GridViewToExcel(gridView1);
        }

        private void 导有TP清单ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Lamex.Common.ExecelHelper.GridViewToExcel(gridView2);
        }
    }
}